Lab 4 - Express - CRUD Operations

COMP7270 - Web and Mobile Programming - HKBU - Spring2025

Today, we are going to develop the other CRUD (Create, Read, Update, Delete) operations, as well as some other more advanced ones.

MongoDB Compass

To interact with data managed by MongoDB, there are some GUI applications that we can consider, notably Studio 3T Free and MongoDB Compass. Our lab already has the latter installed, so let's start it up and provide our connection string.

Screenshot 2024-02-01 at 9.52.02 PM.png

Reading a Single Booking

As we want to read a single item here, we are going to specify the primary key of the document in the URL, in the form of http://localhost:3000/booking/read/63316ae891a81c2419ec2c09, where 63316ae891a81c2419ec2c09 is a dynamic path parameter indicating the primary key of the document.

In index.js, we specify :id as the path parameter. To capture this path parameter, we can use req.params.id, as shown below:

/* Display a single Booking */
router.get('/booking/read/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    let result = await db.collection("bookings").findOne({ _id: new ObjectId(req.params.id) });
    if (result) {
      res.render('booking', { booking: result });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});
/* Display a single Booking */
router.get('/booking/read/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    let result = await db.collection("bookings").findOne({ _id: new ObjectId(req.params.id) });
    if (result) {
      res.render('booking', { booking: result });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});

The route is defined as /booking/read/:id, where :id is a dynamic path parameter representing the primary key of the booking document.

The async function is invoked when this route is accessed.

The connectToDB function is called to establish a connection to the MongoDB (Cosmos) database.

Inside a try block, the findOne method is used to query the bookings collection in the database. The query filters the document by the _id field, which is matched with the value of ObjectId(req.params.id). This converts the path parameter to a valid ObjectId.

If a matching booking document is found, it is passed to the res.render method to render a template named booking. The booking document is provided as data to the template using the booking key.

If no matching booking document is found, a 404 status code is sent with a JSON response containing the message: Booking not found.

If any error occurs during the execution of the try block, a 400 status code is sent with a JSON response containing the error message.

In the finally block, the database connection is closed using db.client.close() to release the resources.

Exercise: Let's develop the /views/booking.ejs file to display the requested booking.

Deleting a Single Booking

Develop another route handler as follows

// Delete a single Booking
router.post('/booking/delete/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    let result = await db.collection("bookings").deleteOne({ _id: new ObjectId(req.params.id) });
    if (result.deletedCount > 0) {
      res.status(200).json({ message: "Booking deleted" });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});
// Delete a single Booking
router.post('/booking/delete/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    let result = await db.collection("bookings").deleteOne({ _id: new ObjectId(req.params.id) });
    if (result.deletedCount > 0) {
      res.status(200).json({ message: "Booking deleted" });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});

Our "delete" route handler looks similar to the previous one.

The code above attempts to delete a document based on the incoming _id. The returned result is an object:

{ acknowledged: true, deletedCount: 1 }
{ acknowledged: true, deletedCount: 1 }

The deletedCount property holds the number of deleted documents. By checking this value, we can determine whether a document has been successfully deleted.

Exercise:

In file booking.ejs, develop a form with a single delete button to delete the booking.

Updating a Single Booking

Update is more complex as it involves two parts:

  1. Showing a form with pre-filled data.
  2. Modifying the stored values based on the form inputs.

Since these two parts use different HTTP request methods and have distinct functionalities, it's recommended to handle them separately with two route handlers. Here's an example of how you can structure the route handlers:

// display the update form
router.get('/booking/update/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    let result = await db.collection("bookings").findOne({ _id: new ObjectId(req.params.id) });
    if (result) {
      res.render('update', { booking: result });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});
// display the update form
router.get('/booking/update/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    let result = await db.collection("bookings").findOne({ _id: new ObjectId(req.params.id) });
    if (result) {
      res.render('update', { booking: result });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});

Create a new file update.ejs and copy the code from index.html. Locate the text fields and set their value attributes to their corresponding booking property.

The select elements, radio buttons and checkbox require special treatments.

The following lines make use of the query selector to locate these elements that should be selected or checked.

Create a new file named update.ejs and copy the code from index.html. Locate the text fields and set their value attributes to their corresponding booking property.

For the select elements, radio buttons, and checkboxes, they require special handling.

The provided code snippet showcases the usage of query selectors in JavaScript to locate specific elements and perform operations on them. It begins by listening for the DOMContentLoaded event to ensure the document has finished loading. Then, it uses query selectors to find elements that match certain criteria.

document.addEventListener("DOMContentLoaded", function () {
    document.querySelector("option[value='<%= booking.team %>']").selected = true;
    teamSelected('<%= booking.team %>')
    document.querySelector("option[value='<%= booking.superhero %>']").selected = true;
    document.querySelector("input[value='<%= booking.payment %>']").checked = true;
    document.querySelector("#gridCheck1").checked = "<%= booking.terms %>" == "true";
});
document.addEventListener("DOMContentLoaded", function () {
    document.querySelector("option[value='<%= booking.team %>']").selected = true;
    teamSelected('<%= booking.team %>')
    document.querySelector("option[value='<%= booking.superhero %>']").selected = true;
    document.querySelector("input[value='<%= booking.payment %>']").checked = true;
    document.querySelector("#gridCheck1").checked = "<%= booking.terms %>" == "true";
});

Notice that the action of the form should also be updated to /booking/update/<%= booking._id %>. Then, let's implement the following route handler to receive the updated data:

// Update a single Booking
router.post('/booking/update/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    req.body.numTickets = parseInt(req.body.numTickets);
    req.body.terms = req.body.terms? true : false;
    req.body.superhero = req.body.superhero || "";
    req.body.modified_at = new Date();

    let result = await db.collection("bookings").updateOne({ _id: new ObjectId(req.params.id) }, { $set: req.body });

    if (result.modifiedCount > 0) {
      res.status(200).json({ message: "Booking updated" });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});
// Update a single Booking
router.post('/booking/update/:id', async function (req, res) {
  const db = await connectToDB();
  try {
    req.body.numTickets = parseInt(req.body.numTickets);
    req.body.terms = req.body.terms? true : false;
    req.body.superhero = req.body.superhero || "";
    req.body.modified_at = new Date();

    let result = await db.collection("bookings").updateOne({ _id: new ObjectId(req.params.id) }, { $set: req.body });

    if (result.modifiedCount > 0) {
      res.status(200).json({ message: "Booking updated" });
    } else {
      res.status(404).json({ message: "Booking not found" });
    }
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});

Here, we use the updateOne() method to update the booking record. This function expects a filter object and a new document object to be provided. Additionally, we can determine whether a document has been updated by examining the result.modifiedCount property.

Generate Test Data

Let's generate 1000 bookings with Mockaroo and import them to Azure Cosmos DB via MongoDB Compass:

  1. Go to the Mockaroo website (https://www.mockaroo.com) and sign up for an account if you haven't already.

  2. Once logged in, click on Schemas in the navigation bar and then click on the New Schema button to create a new schema for your bookings.

  3. Define the fields for your bookings schema like _id, email, numTickets, team, superhero, etc. Set the data types and any specific options for each field as needed.

    Iron Man, Captain America, Thor, Hulk, Black Widow, Hawkeye, Superman, Batman, Wonder Woman, Flash, Aquaman, Cyborg,

Screenshot 2024-02-01 at 9.44.01 PM.png

Screenshot 2023-09-21 at 10.43.09 AM.png

  1. After defining the schema, click on the Preview button to see a preview of the generated data based on your schema. Verify that the generated data looks correct.

  2. Next, set the number of rows to 1000 by modifying the value in the # Rows field.

  3. Choose the desired format for your data. In this case, select the JSON format.

  4. Click on the Download button to download the generated JSON file containing 1000 bookings.

  5. Open MongoDB Compass and select the bookingsDB database and then the bookings collection.

  6. With the collection selected, click on the Add Data button in the toolbar, and choose Import JSON or CSV.

  7. In the import dialog, select the JSON file you downloaded from Mockaroo as the data source.

  8. Click on the Import button to start the import process. MongoDB Compass will import the data from the JSON file into the specified collection in Azure Cosmos DB.

Searching

Next, we develop a search route handler that allows users to search for items based on email, number of tickets, or both:

// Search Bookings
router.get('/booking/search', async function (req, res) {
  const db = await connectToDB();
  try {
    let query = {};
    if (req.query.email) {
      query.email = req.query.email;
    }
    if (req.query.numTickets) {
      query.numTickets = parseInt(req.query.numTickets);
    }

    let result = await db.collection("bookings").find(query).toArray();
    res.render('bookings', { bookings: result });
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});
// Search Bookings
router.get('/booking/search', async function (req, res) {
  const db = await connectToDB();
  try {
    let query = {};
    if (req.query.email) {
      query.email = req.query.email;
    }
    if (req.query.numTickets) {
      query.numTickets = parseInt(req.query.numTickets);
    }

    let result = await db.collection("bookings").find(query).toArray();
    res.render('bookings', { bookings: result });
  } catch (err) {
    res.status(400).json({ message: err.message });
  } finally {
    await db.client.close();
  }
});

The router handler is responsible for performing a search based on the provided query parameters, specifically email and numTickets. To facilitate this, an empty object named query is created, and properties are added to it if the corresponding query parameters are present.

To test this route handler, you can utilize the URL http://localhost:3000/booking/search along with a query string like ?email=tony@stark.com&numTickets=2.

To further improve the implementation, we can introduce partial matching, which allows users to provide a partial string for searching. This can be achieved by modifying the line as follows:

query.email = { $regex: req.query.email };
query.email = { $regex: req.query.email };

By including the $regex operator, partial matching is enabled. Without this keyword, only exact matching would be performed. With the modified line, the value of req.query.email will be treated as a regular expression pattern, allowing for partial matching during the search process.

Pagination

When dealing with a large number of documents in a database collection, it's often preferable not to display all of them on a single page. To address this, we can utilize query parameters, specifically perPage and page, which determine the number of items to be shown per page and the specified page number, respectively.

To implement the desired route handler, consider the following:

// Pagination based on query parameters page and limit, also returns total number of documents
router.get('/booking/paginate', async function (req, res) {
  const db = await connectToDB();
  try {
    let page = parseInt(req.query.page) || 1;
    let perPage = parseInt(req.query.perPage) || 10;
    let skip = (page - 1) * perPage;

    let result = await db.collection("bookings").find().skip(skip).limit(perPage).toArray();
    let total = await db.collection("bookings").countDocuments();

    res.render('paginate', { bookings: result, total: total, page: page, perPage: perPage });
  } catch (err) {
    res.status(400).json({ message: err.message });
  }
  finally {
    await db.client.close();
  }
});
// Pagination based on query parameters page and limit, also returns total number of documents
router.get('/booking/paginate', async function (req, res) {
  const db = await connectToDB();
  try {
    let page = parseInt(req.query.page) || 1;
    let perPage = parseInt(req.query.perPage) || 10;
    let skip = (page - 1) * perPage;

    let result = await db.collection("bookings").find().skip(skip).limit(perPage).toArray();
    let total = await db.collection("bookings").countDocuments();

    res.render('paginate', { bookings: result, total: total, page: page, perPage: perPage });
  } catch (err) {
    res.status(400).json({ message: err.message });
  }
  finally {
    await db.client.close();
  }
});

This route handler fetches paginated bookings data from a database collection by skipping a specified number of records using skip, limiting the number of returned records using limit, and obtaining the total count of items using countDocuments. The paginated results are then rendered using a view template.

Restart the server and test this new route with http://localhost:3000/booking/paginate?perPage=2&page=2. This should display the third and fourth bookings.

To implement pagination links, you can create a new view file named paginate.ejs based on the existing bookings.ejs file. Add the following for loop to generate the pagination links:

<% for (let i = 1; i <= Math.ceil(total / perPage); i++) { %>
  <% if(i === page) { %>
    <span><%= i %></span>
  <% } else { %>
    <a href="/booking/paginate?page=<%= i %>&perPage=<%= perPage %>"><%= i %></a>
  <% } %>
<% } %>
<% for (let i = 1; i <= Math.ceil(total / perPage); i++) { %>
  <% if(i === page) { %>
    <span><%= i %></span>
  <% } else { %>
    <a href="/booking/paginate?page=<%= i %>&perPage=<%= perPage %>"><%= i %></a>
  <% } %>
<% } %>

Modify the route handler so it now renders the paginate.ejs view template instead of bookings.ejs. After making this modification, visit the same URL http://localhost:3000/booking/paginate?perPage=2&page=2, and you should see the third and fourth bookings displayed along with the pagination links at the bottom of the page.

Appendix:

01/02/2024 21:52